package au.edu.unsw.editingtool.db;

/**
 * @author r.chen
 * 
 * DB Layer
 * 
 */

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.LinkedList;

import au.edu.unsw.editingtool.global.EditingToolException;
import au.edu.unsw.editingtool.global.EditingToolGlobalConstant;
import au.edu.unsw.editingtool.global.EditingToolPropertyReader;
import au.edu.unsw.editingtool.global.EditingToolUtil;

class ValuePair {
	public String variable;
	public String value;
	
	public ValuePair() {
		this.variable = this.value = "";
	}
	
	public ValuePair(String variable, String value) {
		this.variable = variable;
		this.value = value;
	}
}

public class DBManager {
	protected String DBServer 	   = "//149.171.40.42:3306/";
	protected String DBUser   	   = "reader";
	protected String DBPsword 	   = "test123?";
	protected String DBDatabase   = "fedora22";
	protected String MappingErrorMsg = "Mapping can't be found";
	
	protected Hashtable<String, String> Conjunctions  = new Hashtable<String, String>();
	protected Hashtable<String, String> Tables = new Hashtable<String, String>();
	protected LinkedList<ValuePair> Conditions = new LinkedList<ValuePair>();
	protected String ConditionString = "";
	protected ArrayList<String> Fields = new ArrayList<String>();
	//protected SortingOrder Orders = new SortingOrder();
	protected LinkedList<ValuePair> Orders = new LinkedList<ValuePair>();
	protected Hashtable<String, String> ColumnValues = new Hashtable<String, String>();
	protected Connection Conn 		= null;
	protected ResultSet ResultSet 	= null;
	protected Statement Stmt		= null;
	protected Hashtable<String, String> MappingTable = new Hashtable<String, String>();
	protected String Result[][] = null;
	private String Limit[] = null;
	private String TotalRecord = "0";
	private String propertyFile = "";
	
	protected String table = "";
	protected String primaryKeys[] = null;
	
	public void connDB() throws ClassNotFoundException, SQLException {
		
		Class.forName("com.mysql.jdbc.Driver");
		String connUrl = "jdbc:mysql:" + 
						  DBServer + 
						  DBDatabase;			
		Conn = DriverManager.getConnection(connUrl, DBUser, DBPsword);		
	}
	
	public void initDB() throws ClassNotFoundException, SQLException {
		EditingToolPropertyReader reader = new EditingToolPropertyReader();
		reader.setFile(this.propertyFile);
		reader.setFilter(EditingToolPropertyReader.DATABASE);
		try {
			reader.retrieveProperties();
		} catch (IOException e) {
			EditingToolException ex = new EditingToolException();
			ex.setMessage("Getting Error When Process Property File.");
			e.printStackTrace();
		}
		Hashtable<String, String> properties = reader.getProperties();
		this.setDBHost("//" + properties.get("host") + ":" + properties.get("port") + "/");
		this.setDBSchema(properties.get("schema"));
		this.setDBUser(properties.get("username"));
		this.setDBPassword(properties.get("password"));
		this.connDB();
	}
	
	public void setConfFile(String file) {
		this.propertyFile = file;
	}
	
	public void setDBUser(String user) {
		this.DBUser = user;
	}
	
	public void setDBPassword(String password) {
		this.DBPsword = password;
	}
	
	public void setDBHost(String host) {
		this.DBServer = host;
	}
	
	public void setDBSchema(String schema) {
		this.DBDatabase = schema;
	}
	
	private String getMappingField(String object) throws EditingToolException {
		if ((this.MappingTable.get(object) == null) || (this.MappingTable.get(object) == "")) {
			throw  new EditingToolException();
		}
		else
			return this.MappingTable.get(object); 
	}
	
	public void setValidRecordsOnly() {
		//state not like "i"
		String validFilter = "state";
		String validCondition = " not like \"i\" ";
		if (this.ConditionString.trim().length() > 0)
			this.ConditionString += " and " + validFilter + validCondition;
		else
			Conditions.add(new ValuePair(validFilter, validCondition));
	}
	
	public void addConjunction(int index, String conjunction) {
		Conjunctions.put(index + "", conjunction);
	}
	
	public void setType(String viaType) throws EditingToolException {
		String type = "type";
		if (viaType.equalsIgnoreCase("book")) {	//hard code in			
			Conditions.add(new ValuePair(this.getMappingField(type), " not like \"%book chapter%\" "));					
		}
		Conditions.add(new ValuePair(this.getMappingField(type), setWildcard(viaType)));
	}
	
	public void setTypes(String types[]) throws EditingToolException {
		String type = "type";
		String cond = "";
		String glue = " or ";
		for (int i = 0; i < types.length; i++) {
			if (types[i].equalsIgnoreCase("book")) { //hard code in
				cond += " ( " + this.getMappingField(type) + " " + " not like \"%book chapter%\" and " + 
						this.getMappingField(type) + " " + setWildcard(types[i]) + " ) " ;
			} else {
				cond += this.getMappingField(type) + " " + setWildcard(types[i]);
			}
			cond += glue;
		}
		if (cond.endsWith(glue))
			cond = cond.substring(0, cond.length() - glue.length());
		cond = " ( " + cond + " )";
		Conditions.add(new ValuePair(cond, ""));
	}
	
	public void setColumnValue(String column, String value) throws EditingToolException {
		ColumnValues.put(this.getMappingField(column), value);
	}
	
	public String setWildcard(String value) {
		return " like \"%" + value + "%\"";
	}
	
	public String setEqual(String value) {
		return " = \"" + value + "\"";
	}
	
	public void setTable(String table, String alias) {
		Tables.put(table, alias);
	}
	
	public static String setOtherConditions(String value, String condition) {
		return condition + " \"" + value + "\"";
	}
	
	public void setFieldsCondition(String field, String clause) throws EditingToolException  {		
		Conditions.add(new ValuePair(this.getMappingField(field), clause));
	}
	
	public void setFieldsCondition(String[] fields, String value) throws EditingToolException  {
		String cond = "";
		String glue = " or ";
		for (int i = 0; i < fields.length; i++) 
			cond += this.getMappingField(fields[i]) + " " + setWildcard(value) + glue;
		if (cond.endsWith(glue))
			cond = cond.substring(0, cond.length() - glue.length());
		Conditions.add(new ValuePair(cond, ""));
	}

	protected void setCondition(String field, String clause, String tableAlias, String operator) {		
		return;
	}

	public void setOrder(LinkedList<ValuePair> so) throws EditingToolException {
		Iterator<ValuePair> iter = so.iterator();		
		while (iter.hasNext()) {
			ValuePair obj = iter.next();
			this.Orders.add(new ValuePair(this.getMappingField(obj.variable), this.getSortOrder(obj.value)));
		}
	}
	
	private String getSortOrder(String isASC) {
		String asc = "ASC";
		String desc = "DESC";
		if (isASC.equalsIgnoreCase(asc))
			return asc;
		else 
			return desc;
	}
	
	public void addField(String field[]) throws EditingToolException {
		for (int i = 0; i < field.length; i++) {
			String dbField = this.getMappingField(field[i]);
			if (!Fields.contains(dbField))
				Fields.add(dbField);
		}
	}
	
	protected String[] pileUp(Hashtable<String, String> tb){
		String pile[] = new String[tb.size()];
		
		Iterator<String> iter = tb.keySet().iterator();		
		for (int i = 0; iter.hasNext(); i++) {
			Object key = iter.next();
			pile[i] = key.toString() + " " + tb.get(key);
		}
		return pile;
	}
	
	protected String join(String in[], String delimiter) {
		String result = "";
		for (int i = 0; i < in.length; i++) {
			result += in[i] + delimiter;
		}
		if (result.endsWith(delimiter))
			result = result.substring(0, result.length() - delimiter.length());
		return result;
	}
	
	private String pileUp(Hashtable<String, String> tb, String delimiter) {
		String str = "";
		Iterator<String> iter = tb.keySet().iterator();
		for (int i = 0; iter.hasNext(); i++) {
			Object key = iter.next();
			str += key.toString() + " " + tb.get(key) + delimiter;
		}
		if (str.endsWith(delimiter))
			str = str.substring(0, str.length() - delimiter.length());
		return str;
	}
	
	public String getConditions() {
		Iterator<ValuePair> iter = Conditions.iterator();
		String str = "";
		String delimiter = " and ";
		String joint	 = "";
		int index = 0;
		while (iter.hasNext()) {
			ValuePair obj = iter.next();
			String cond = " ( " + obj.variable + " " + obj.value + " ) ";
			if (index == 0)
				;
			else if (Conjunctions.get(index + "") != null) {
				joint =  " " + Conjunctions.get(index + "") + " ";
			}
			else {
				joint = delimiter;//str += delimiter;
			}
			str += joint + cond;
			index++;
		}
		return str;
	}
	
	protected String getFields() throws EditingToolException {
		if (Fields.size() == 0)
			throw new EditingToolException();
		Iterator<String> iter = Fields.iterator();
		String fields = " ";
		while (iter.hasNext()) {
			Object field = iter.next();
			fields += field.toString() + ",";
		}
		return fields.substring(0, fields.length() - 1) + " ";
	}
	
	public void updateExpressPath(String sql, boolean generateAutoKey) throws SQLException{
		Stmt = Conn.createStatement();
		int autoKeyDecision = (generateAutoKey) ? Statement.RETURN_GENERATED_KEYS: Statement.NO_GENERATED_KEYS;
		Stmt.executeUpdate(sql, autoKeyDecision);
	}
	
	protected void setColumnValues(Hashtable<String, String> info) throws EditingToolException {
		Iterator<String> iterator = info.keySet().iterator();
		while (iterator.hasNext()) {
			String key = iterator.next();
			String value = info.get(key); 
			if ((value != null) && (value.trim().length() > 0))
				this.setColumnValue(key, value);
		}
	}
	
	// if insert encounter duplicated, then it will do an update via primary key.
	public void insert(boolean generateAutoKey, boolean duplicateDoUpdate) throws SQLException, EditingToolException {
		try {
			this.insertRecords(generateAutoKey);
		} catch (SQLException e) {
			if (e.getErrorCode() != EditingToolGlobalConstant.mySQLDuplicateErrorCode)
				throw e;
			else {
				if (!duplicateDoUpdate)
					return;
				this.cleanResults();
				for (int i = 0; i < this.primaryKeys.length; i++) {
					this.setFieldsCondition(this.primaryKeys[i], 
											this.setEqual(this.ColumnValues.get(this.primaryKeys[i])));
					this.ColumnValues.remove(this.primaryKeys[i]);
				}
				this.updateRecords();
			}
		}
	}
	
	private void insertRecords(boolean generateAutoKey) throws SQLException {
		Stmt = Conn.createStatement();
		String INSERT = "insert into ";
		String[] fields = this.getColumns();
		String[] values = this.getValues(fields);
		
		INSERT += this.pileUp(Tables, "");
		
		if ((fields == null) || (fields.length == 0))
			;
		else {
			INSERT += " ( " + EditingToolUtil.join(fields, ", ", "", "") + " ) ";
		}
		
		INSERT += " values ";
		
		if ((values == null) || (values.length == 0))
			throw  new SQLException();
		else {
			INSERT += " ( " + EditingToolUtil.join(values, ", ", "\"", "\"") + " ) ";
		}
		
		int autoKeyDecision = (generateAutoKey) ? Statement.RETURN_GENERATED_KEYS: Statement.NO_GENERATED_KEYS;
		
		Stmt.executeUpdate(INSERT, autoKeyDecision);
	}
	
	public String getGenerateKey() throws SQLException {
		ResultSet keys = Stmt.getGeneratedKeys();
		return keys.next() ? keys.getString(1): null;
	}
	
	public void updateRecords() throws SQLException {
		Stmt = Conn.createStatement();
		String UPDATE = "update ";
		String[] fields = this.getColumns();
		String[] values	= this.getValues(fields);
		
		UPDATE += this.pileUp(Tables, "");
		
		UPDATE += " set ";
		int index = 0;
		for (; index < fields.length - 1; index++) {
			UPDATE += " " + fields[index] + " = " + "\"" + values[index] + "\" " 
				   + " and ";
		}
		UPDATE += " " + fields[index] + " = " + "\"" + values[index] + "\" ";
		
		String conds  = (this.ConditionString.length() == 0) ? this.getConditions() : this.ConditionString;
		if (conds.length() > 0)
			UPDATE += " where " + conds + " ";
		
		Stmt.executeUpdate(UPDATE);
	}
	
	private String[] getColumns(){
		String result[] = new String[ColumnValues.size()];
		int i = 0;
		for (Enumeration<String> keys = ColumnValues.keys(); keys.hasMoreElements(); i++) {
			result[i] = keys.nextElement();
		}
		return result;
	}
	
	private String[] getValues(String fields[]) {
		String result[] = new String[fields.length];
		for (int i = 0; i < fields.length; i++) {
			result[i] = ColumnValues.get(fields[i]);
		}
		return result;
	}
	
	protected String getOrders() {
		String orders = "";
		String delimiter = ",";
		String orderBy = "";
		Iterator<ValuePair> iter = this.Orders.iterator();
		while (iter.hasNext()) {
			orderBy = " order by ";
			ValuePair obj = iter.next();
			orders += obj.variable + " " + obj.value + delimiter;
		}
		if (orders.endsWith(delimiter))
			orders = orders.substring(0, orders.length() - delimiter.length());
		return orderBy + orders;
	}
	
	public void setConditionString(String cond) {
		this.ConditionString = cond;
	}
	
	public void setLimit(String limit[]) {
		this.Limit = limit;
	}
	
	public boolean selectRecords() throws SQLException {		
		
		Stmt = Conn.createStatement();
		String sql = "";
		try {
			sql = "Select " + this.getFields();
		} catch (EditingToolException e) {
			e.setMessage("Getting Error When Try to Get Fields.");
			e.printStackTrace();
		}
		this.setValidRecordsOnly();
		String conds  = (this.ConditionString.length() == 0) ? this.getConditions() : this.ConditionString;
		sql += " from " + this.pileUp(Tables, ", ") + " ";
		if (conds.length() > 0)
			sql += " where " + conds + " ";		
		String orders = this.getOrders();
		if (orders.length() > 0)
			sql += " " + orders;
		if (this.Limit != null)
			sql += " LIMIT " + this.Limit[0] + ", " + this.Limit[1];
		
		System.out.println("[Query]: " + sql);
		ResultSet = Stmt.executeQuery(sql);
		return true;
	}
	
	public String getTotalRecordsRelated() throws SQLException {
		String countTotal = " count(*) ";
		Stmt = Conn.createStatement();
		String sql = "Select " + countTotal;
		this.setValidRecordsOnly();
		String conds  = (this.ConditionString.length() == 0) ? this.getConditions() : this.ConditionString;
		sql += " from " + this.pileUp(Tables, ", ") + " ";
		if (conds.length() > 0)
			sql += " where " + conds + " ";
		System.out.println("[Query]: " + sql);
		ResultSet = Stmt.executeQuery(sql);
		
		if (ResultSet.next()) 
			TotalRecord = ResultSet.getString(1);
		
		return TotalRecord;
	}
	
	public void updateSQL(String sql) throws SQLException {
		Conn.createStatement().executeUpdate(sql);
	}
	
	public void querySQL(String sql) throws SQLException {
		ResultSet = Conn.createStatement().executeQuery(sql);
	}
	
	public void insertSQL(String sql, boolean generateAutoKey) throws SQLException {
		int autoKeyDecision = (generateAutoKey) ? Statement.RETURN_GENERATED_KEYS: Statement.NO_GENERATED_KEYS;
		Conn.createStatement().executeUpdate(sql, autoKeyDecision);
	}
	
	public void deleteSQL(String sql) throws SQLException {
		Conn.createStatement().executeUpdate(sql);
	}
	
	public void deleteRecord() throws SQLException {
		Stmt = Conn.createStatement();
		String delete = "delete from ";
		
		delete += this.pileUp(Tables, "");
		
		String conds  = (this.ConditionString.length() == 0) ? this.getConditions() : this.ConditionString;
		if (conds.length() > 0)
			delete += " where " + conds + " ";
		
		Stmt.executeUpdate(delete);
	}
	
	
	protected boolean collectResults() throws SQLException {
		if (this.ResultSet.last()) {
			this.Result = new String[this.ResultSet.getRow()][this.Fields.size()];
			this.ResultSet.beforeFirst();
		} else {
			return false;
		}
		
		for (int i = 0; this.ResultSet.next(); i++) {
			for (int j = 1; j <= this.Fields.size(); j++) {
				this.Result[i][j - 1] = this.ResultSet.getString(j);
			}
		}
		return true;
	}
/*	
	public String[][] getResultSet() throws SQLException {
		if (this.ResultSet == null)
			return null;
		//this.sortResults();
		this.getResults();
		return this.Result;
	}
*/
	// don't forget to do clean.
	public String[][] getInfoViaPrimaryKeys(Hashtable<String, String> keys, String fields[]) throws SQLException, ClassNotFoundException {
		
		this.setTable(this.table, "");
		for (int i = 0; i < this.primaryKeys.length; i++) {
			this.setFieldsCondition(this.primaryKeys[i], keys.get(this.primaryKeys[i]));
		}
		this.addField(fields);
		
		this.initDB();
		this.selectRecords();
		if (this.ResultSet == null) {
			return null;
		}
		if (!this.collectResults()) {
			return null;
		}
		return this.Result;
	}
	
	public void cleanResults() throws SQLException {
		if (ResultSet != null)
			ResultSet.close();
		if (Stmt != null)
			Stmt.close();
		Result 		= null;
		TotalRecord = "0";
	}
	
	public void cleanResource() throws SQLException {
		table = "";
		primaryKeys = null;
		Tables = new Hashtable<String, String>();
		ColumnValues = new Hashtable<String, String>();
		Conditions  = new LinkedList<ValuePair>();
		Conjunctions = new Hashtable<String, String>();
		ConditionString = "";
		Limit		= null;
		this.cleanResults();
	}
	
	public void clean() throws SQLException {
		this.cleanResource();
		this.Conn.close();
	}
}
